package sqlserver

import (
	"github.com/viant/sqlx/metadata/database"
	"github.com/viant/sqlx/metadata/info"
	"github.com/viant/sqlx/metadata/info/dialect"
	"github.com/viant/sqlx/metadata/registry"
	"log"
)

const product = "SQLServer"
const driver = "Driver"
const driverPkg = "mssql"

var sqlServer = database.Product{
	Name:      product,
	DriverPkg: driverPkg,
	Driver:    driver,
	//Major:     int,
	//Minor:     int,
	//Release:   int
}

//SQLServer return SQLServer product
func SQLServer() *database.Product {
	return &sqlServer
}

func init() {
	err := registry.Register(
		info.NewQuery(info.KindVersion, "SELECT CONCAT('SQLServer - ',CONVERT(varchar(4000), SERVERPROPERTY('ProductVersion')))", sqlServer),

		info.NewQuery(info.KindCatalogs, "SELECT NAME AS CATALOG_NAME FROM SYS.DATABASES", sqlServer),

		info.NewQuery(info.KindCatalog, "SELECT T.CATALOG_NAME FROM (SELECT NAME AS CATALOG_NAME FROM SYS.DATABASES) T", sqlServer,
			info.NewCriterion(info.Catalog, "T.CATALOG_NAME")),

		info.NewQuery(info.KindCurrentSchema, `SELECT DB_NAME() AS CATALOG_NAME, SCHEMA_NAME() AS SCHEMA_NAME`, sqlServer),

		// Dynamic SQL to get all catalogs with schemas
		info.NewQuery(info.KindSchemas, schemasDynamicSQLQuery, sqlServer,
			info.NewCriterion(info.Catalog, "CATALOG_NAME"),
		),

		// Dynamic SQL to get all catalogs with schemas
		info.NewQuery(info.KindSchema, schemasDynamicSQLQuery, sqlServer,
			info.NewCriterion(info.Catalog, "CATALOG_NAME"),
			info.NewCriterion(info.Schema, "SCHEMA_NAME"),
		),

		info.NewQuery(info.KindTables, `WITH ROWS_CNT AS
(
SELECT SCHEMA_NAME(SCHEMA_ID) TABLE_SCHEMA,
       T.NAME AS TABLE_NAME,
       SUM(P.ROWS) TOTAL_ROW_COUNT
  FROM $Args[0].SYS.TABLES T
  JOIN $Args[0].SYS.PARTITIONS P 
    ON T.OBJECT_ID = P.OBJECT_ID
   AND P.INDEX_ID IN ( 0, 1 )
 GROUP BY SCHEMA_NAME(SCHEMA_ID), T.NAME
) 
SELECT 
T2.TABLE_CATALOG,
T2.TABLE_SCHEMA,
T2.TABLE_NAME,
COALESCE(D.VALUE,'') TABLE_COMMENT,
T2.TABLE_TYPE,
COALESCE(C.IS_IDENTITY, 0) AUTO_INCREMENT,
T1.CREATE_DATE CREATE_TIME,
T1.MODIFY_DATE UPDATE_TIME,
R.TOTAL_ROW_COUNT  TABLE_ROWS,
'' VERSION,
'' ENGINE,
'' DDL
FROM $Args[0].SYS.TABLES T1
JOIN $Args[0].INFORMATION_SCHEMA.TABLES T2 ON T2.TABLE_TYPE = 'BASE TABLE' AND T2.TABLE_SCHEMA = SCHEMA_NAME(T1.SCHEMA_ID) AND T2.TABLE_NAME = T1.NAME
LEFT JOIN $Args[0].SYS.COLUMNS C ON C.OBJECT_ID = T1.OBJECT_ID AND C.IS_IDENTITY = 1
LEFT JOIN $Args[0].SYS.EXTENDED_PROPERTIES D ON T1.OBJECT_ID = D.MAJOR_ID AND D.NAME = 'TableDescription'
LEFT JOIN ROWS_CNT R ON T2.TABLE_NAME = R.TABLE_NAME AND T2.TABLE_SCHEMA = R.TABLE_SCHEMA`,
			sqlServer,
			info.NewCriterion(info.Catalog, "T2.TABLE_CATALOG"),
			info.NewCriterion(info.Schema, "T2.TABLE_SCHEMA"),
		),

		info.NewQuery(info.KindTable, `WITH CPK AS (
SELECT 
    C.OBJECT_ID,
    C.COLUMN_ID,
    C.NAME AS COLUMN_NAME, 
    PK.NAME AS PK_NAME,
    IC.INDEX_COLUMN_ID,
    T1.NAME AS TABLE_NAME,
    PK.IS_PRIMARY_KEY
FROM $Args[0].SYS.TABLES T1
JOIN $Args[0].SYS.INDEXES PK ON T1.OBJECT_ID = PK.OBJECT_ID AND PK.IS_PRIMARY_KEY = 1
JOIN $Args[0].SYS.INDEX_COLUMNS IC ON IC.OBJECT_ID = PK.OBJECT_ID AND IC.INDEX_ID = PK.INDEX_ID
JOIN $Args[0].SYS.COLUMNS C ON PK.OBJECT_ID = C.OBJECT_ID AND C.COLUMN_ID = IC.COLUMN_ID
)
SELECT 
T2.TABLE_CATALOG,
T2.TABLE_SCHEMA,
T2.TABLE_NAME,
C2.COLUMN_NAME,
C2.ORDINAL_POSITION,
COALESCE(D.VALUE, '') COLUMN_COMMENT,
C2.DATA_TYPE,
C2.CHARACTER_MAXIMUM_LENGTH,
C2.NUMERIC_PRECISION,
C2.NUMERIC_SCALE,
C2.IS_NULLABLE,
C2.COLUMN_DEFAULT,
CASE WHEN COALESCE(CPK.IS_PRIMARY_KEY, 0) = 1 THEN 'PRI' ELSE '' END COLUMN_KEY,
'' DESCENDING,
COALESCE(CPK.PK_NAME, '') INDEX_NAME,
COALESCE(CPK.INDEX_COLUMN_ID, 0) INDEX_POSITION,
C2.COLLATION_NAME COLLATION
FROM $Args[0].SYS.TABLES T1
JOIN $Args[0].INFORMATION_SCHEMA.TABLES T2 ON T2.TABLE_TYPE = 'BASE TABLE' AND T2.TABLE_SCHEMA = SCHEMA_NAME(T1.SCHEMA_ID) AND T2.TABLE_NAME = T1.NAME
JOIN $Args[0].INFORMATION_SCHEMA.COLUMNS  C2 ON C2.TABLE_CATALOG = T2.TABLE_CATALOG AND C2.TABLE_SCHEMA = T2.TABLE_SCHEMA AND C2.TABLE_NAME = T2.TABLE_NAME
JOIN $Args[0].SYS.COLUMNS C ON C.OBJECT_ID = T1.OBJECT_ID AND C.NAME = C2.COLUMN_NAME
LEFT JOIN $Args[0].SYS.EXTENDED_PROPERTIES D ON T1.OBJECT_ID = D.MAJOR_ID AND C.COLUMN_ID = D.MINOR_ID AND D.NAME IN ('ColumnDescription')
LEFT JOIN CPK ON CPK.OBJECT_ID = C.OBJECT_ID AND CPK.COLUMN_ID = C.COLUMN_ID`,
			sqlServer,
			info.NewCriterion(info.Catalog, "T2.TABLE_CATALOG"),
			info.NewCriterion(info.Schema, "T2.TABLE_SCHEMA"),
			info.NewCriterion(info.Table, "T2.TABLE_NAME"),
		),

		info.NewQuery(info.KindViews, `SELECT 
T2.TABLE_CATALOG,
T2.TABLE_SCHEMA,
T2.TABLE_NAME,
'' TABLE_COMMENT,
T1.TYPE TABLE_TYPE,
COALESCE(C.IS_IDENTITY, 0) AUTO_INCREMENT,
T1.CREATE_DATE CREATE_TIME,
T1.MODIFY_DATE UPDATE_TIME,
0 AS TABLE_ROWS,
'' VERSION,
'' ENGINE,
'' DDL
FROM $Args[0].SYS.VIEWS T1
JOIN $Args[0].INFORMATION_SCHEMA.VIEWS T2 ON  T2.TABLE_SCHEMA = SCHEMA_NAME(T1.SCHEMA_ID) AND T2.TABLE_NAME = T1.NAME
LEFT JOIN $Args[0].SYS.COLUMNS C ON C.OBJECT_ID = T1.OBJECT_ID AND C.IS_IDENTITY = 1`,
			sqlServer,
			info.NewCriterion(info.Catalog, "T2.TABLE_CATALOG"),
			info.NewCriterion(info.Schema, "T2.TABLE_SCHEMA"),
		),

		info.NewQuery(info.KindView, `SELECT 
T2.TABLE_CATALOG,
T2.TABLE_SCHEMA,
T2.TABLE_NAME,
C2.COLUMN_NAME,
C2.ORDINAL_POSITION,
'' COLUMN_COMMENT,
C2.DATA_TYPE,
C2.CHARACTER_MAXIMUM_LENGTH,
C2.NUMERIC_PRECISION,
C2.NUMERIC_SCALE,
C2.IS_NULLABLE,
C2.COLUMN_DEFAULT,
'' COLUMN_KEY,
'' DESCENDING,
'' INDEX_NAME,
0 INDEX_POSITION,
C2.COLLATION_NAME COLLATION
FROM $Args[0].SYS.VIEWS T1
JOIN $Args[0].INFORMATION_SCHEMA.VIEWS T2 ON T2.TABLE_SCHEMA = SCHEMA_NAME(T1.SCHEMA_ID) AND T2.TABLE_NAME = T1.NAME
JOIN $Args[0].INFORMATION_SCHEMA.COLUMNS  C2 ON C2.TABLE_CATALOG = T2.TABLE_CATALOG AND C2.TABLE_SCHEMA = T2.TABLE_SCHEMA AND C2.TABLE_NAME = T2.TABLE_NAME`,
			sqlServer,
			info.NewCriterion(info.Catalog, "T2.TABLE_CATALOG"),
			info.NewCriterion(info.Schema, "T2.TABLE_SCHEMA"),
			info.NewCriterion(info.View, "T2.TABLE_NAME"),
		),

		info.NewQuery(info.KindPrimaryKeys, `SELECT 
TC.CONSTRAINT_NAME,  
TC.CONSTRAINT_TYPE,
TC.CONSTRAINT_CATALOG,
TC.CONSTRAINT_SCHEMA,
TC.TABLE_NAME,
IC.INDEX_COLUMN_ID ORDINAL_POSITION,
C.NAME COLUMN_NAME,
'' REFERENCED_TABLE_NAME,
'' REFERENCED_COLUMN_NAME,
'' REFERENCED_TABLE_SCHEMA,
0 POSITION_IN_UNIQUE_CONSTRAINT,
'' ON_UPDATE,
'' ON_DELETE,
'' ON_MATCH
FROM $Args[0].SYS.TABLES T1
JOIN $Args[0].INFORMATION_SCHEMA.TABLES T2 ON T1.NAME = T2.TABLE_NAME
JOIN $Args[0].INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON TC.TABLE_CATALOG = T2.TABLE_CATALOG 
AND TC.TABLE_SCHEMA = T2.TABLE_SCHEMA 
AND TC.TABLE_NAME = T2.TABLE_NAME 
AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
JOIN $Args[0].SYS.INDEXES PK ON T1.OBJECT_ID = PK.OBJECT_ID AND PK.NAME = TC.CONSTRAINT_NAME AND PK.IS_PRIMARY_KEY = 1
JOIN $Args[0].SYS.INDEX_COLUMNS IC ON IC.OBJECT_ID = PK.OBJECT_ID AND IC.INDEX_ID = PK.INDEX_ID
JOIN $Args[0].SYS.COLUMNS C ON PK.OBJECT_ID = C.OBJECT_ID AND C.COLUMN_ID = IC.COLUMN_ID`,
			sqlServer,
			info.NewCriterion(info.Catalog, "TC.CONSTRAINT_CATALOG"),
			info.NewCriterion(info.Schema, "TC.CONSTRAINT_SCHEMA"),
			info.NewCriterion(info.Table, "TC.TABLE_NAME"),
		),

		info.NewQuery(info.KindForeignKeys, `SELECT
TC.CONSTRAINT_NAME,  
TC.CONSTRAINT_TYPE,
TC.CONSTRAINT_CATALOG,
TC.CONSTRAINT_SCHEMA,
FK_TAB.NAME TABLE_NAME,
FK_COLS.CONSTRAINT_COLUMN_ID ORDINAL_POSITION,
FK_COL.NAME COLUMN_NAME, 
PK_TAB.NAME REFERENCED_TABLE_NAME,
PK_COL.NAME  REFERENCED_COLUMN_NAME,
SCHEMA_NAME(PK_TAB.SCHEMA_ID) REFERENCED_TABLE_SCHEMA,
UQC.ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT,
FK2.UPDATE_RULE AS ON_UPDATE,
FK2.DELETE_RULE  AS ON_DELETE,
FK2.MATCH_OPTION AS ON_MATCH
FROM $Args[0].SYS.FOREIGN_KEYS FK1
JOIN $Args[0].SYS.TABLES FK_TAB ON FK_TAB.OBJECT_ID = FK1.PARENT_OBJECT_ID
JOIN $Args[0].SYS.TABLES PK_TAB ON PK_TAB.OBJECT_ID = FK1.REFERENCED_OBJECT_ID
JOIN $Args[0].SYS.FOREIGN_KEY_COLUMNS FK_COLS ON FK_COLS.CONSTRAINT_OBJECT_ID = FK1.OBJECT_ID
JOIN $Args[0].SYS.COLUMNS FK_COL ON FK_COL.COLUMN_ID = FK_COLS.PARENT_COLUMN_ID AND FK_COL.OBJECT_ID = FK_TAB.OBJECT_ID
JOIN $Args[0].SYS.COLUMNS PK_COL ON PK_COL.COLUMN_ID = FK_COLS.REFERENCED_COLUMN_ID AND PK_COL.OBJECT_ID = PK_TAB.OBJECT_ID
JOIN $Args[0].INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK2 ON  FK2.CONSTRAINT_SCHEMA = schema_name(FK1.schema_id) AND FK2.CONSTRAINT_NAME = FK1.NAME
JOIN $Args[0].INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.CONSTRAINT_SCHEMA = FK2.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = FK2.CONSTRAINT_NAME
JOIN $Args[0].INFORMATION_SCHEMA.KEY_COLUMN_USAGE UQC ON UQC.CONSTRAINT_SCHEMA = FK2.UNIQUE_CONSTRAINT_SCHEMA AND UQC.CONSTRAINT_NAME = FK2.UNIQUE_CONSTRAINT_NAME AND UQC.TABLE_NAME = PK_TAB.NAME AND UQC.COLUMN_NAME = PK_COL.NAME
`,
			sqlServer,
			info.NewCriterion(info.Catalog, "FK2.CONSTRAINT_CATALOG"),
			info.NewCriterion(info.Schema, "FK2.CONSTRAINT_SCHEMA"),
			info.NewCriterion(info.Table, "FK_TAB.NAME"),
		),

		info.NewQuery(info.KindConstraints, `WITH CCU2 AS ( 
    SELECT SCHEMA_NAME(T.SCHEMA_ID) AS TABLE_SCHEMA,
    T.NAME AS TABLE_NAME,
    COL.NAME AS COLUMN_NAME,
    COL.COLUMN_ID,
    CON.DEFINITION,
    CASE WHEN CON.IS_DISABLED = 0 
        THEN 'Active' 
        ELSE 'Disabled' 
        END AS STATUS,
    CON.NAME AS CONSTRAINT_NAME
    FROM $Args[0].SYS.CHECK_CONSTRAINTS CON
    LEFT JOIN $Args[0].SYS.OBJECTS T ON CON.PARENT_OBJECT_ID = T.OBJECT_ID
    LEFT JOIN $Args[0].SYS.ALL_COLUMNS COL ON CON.PARENT_COLUMN_ID = COL.COLUMN_ID AND CON.PARENT_OBJECT_ID = COL.OBJECT_ID
)
SELECT
T.CONSTRAINT_NAME,  
T.CONSTRAINT_TYPE,
T.CONSTRAINT_CATALOG,
T.CONSTRAINT_SCHEMA,
T.TABLE_NAME,
T.ORDINAL_POSITION,
T.COLUMN_NAME,
T.REFERENCED_TABLE_NAME,
T.REFERENCED_COLUMN_NAME,
T.REFERENCED_TABLE_SCHEMA,
T.POSITION_IN_UNIQUE_CONSTRAINT,
T.ON_UPDATE,
T.ON_DELETE,
T.ON_MATCH
FROM
(
SELECT /*CHECK*/
TC.CONSTRAINT_NAME,  
TC.CONSTRAINT_TYPE,
TC.CONSTRAINT_CATALOG,
TC.CONSTRAINT_SCHEMA,
TC.TABLE_NAME,
CASE WHEN CCU2.column_name IS NOT NULL THEN 1 ELSE 0 END ORDINAL_POSITION,
CCU.COLUMN_NAME COLUMN_NAME,
'' REFERENCED_TABLE_NAME,
'' REFERENCED_COLUMN_NAME,
'' REFERENCED_TABLE_SCHEMA,
0 POSITION_IN_UNIQUE_CONSTRAINT,
'' ON_UPDATE,
'' ON_DELETE,
'' ON_MATCH
FROM $Args[0].INFORMATION_SCHEMA.TABLES T2 
JOIN $Args[0].INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON T2.TABLE_TYPE = 'BASE TABLE'
AND TC.TABLE_CATALOG = T2.TABLE_CATALOG 
AND TC.TABLE_SCHEMA = T2.TABLE_SCHEMA 
AND TC.TABLE_NAME = T2.TABLE_NAME 
AND (TC.CONSTRAINT_TYPE = 'CHECK')
LEFT JOIN $Args[0].INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU 
ON CCU.TABLE_CATALOG = TC.TABLE_CATALOG 
AND CCU.TABLE_SCHEMA = TC.TABLE_SCHEMA 
AND CCU.TABLE_NAME = TC.TABLE_NAME
AND CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
LEFT JOIN CCU2 
ON CCU2.TABLE_SCHEMA = CCU.TABLE_SCHEMA 
AND CCU2.TABLE_NAME = CCU.TABLE_NAME
AND CCU2.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
AND CCU2.COLUMN_NAME = CCU.COLUMN_NAME
UNION
SELECT /*FK*/
TC.CONSTRAINT_NAME,  
TC.CONSTRAINT_TYPE,
TC.CONSTRAINT_CATALOG,
TC.CONSTRAINT_SCHEMA,
FK_TAB.NAME TABLE_NAME,
FK_COLS.CONSTRAINT_COLUMN_ID ORDINAL_POSITION,
FK_COL.NAME COLUMN_NAME, 
PK_TAB.NAME REFERENCED_TABLE_NAME,
PK_COL.NAME  REFERENCED_COLUMN_NAME,
SCHEMA_NAME(PK_TAB.SCHEMA_ID) REFERENCED_TABLE_SCHEMA,
UQC.ORDINAL_POSITION POSITION_IN_UNIQUE_CONSTRAINT,
FK2.UPDATE_RULE AS ON_UPDATE,
FK2.DELETE_RULE  AS ON_DELETE,
FK2.MATCH_OPTION AS ON_MATCH
FROM $Args[0].SYS.FOREIGN_KEYS FK1
JOIN $Args[0].SYS.TABLES FK_TAB ON FK_TAB.OBJECT_ID = FK1.PARENT_OBJECT_ID
JOIN $Args[0].SYS.TABLES PK_TAB ON PK_TAB.OBJECT_ID = FK1.REFERENCED_OBJECT_ID
JOIN $Args[0].SYS.FOREIGN_KEY_COLUMNS FK_COLS ON FK_COLS.CONSTRAINT_OBJECT_ID = FK1.OBJECT_ID
JOIN $Args[0].SYS.COLUMNS FK_COL ON FK_COL.COLUMN_ID = FK_COLS.PARENT_COLUMN_ID AND FK_COL.OBJECT_ID = FK_TAB.OBJECT_ID
JOIN $Args[0].SYS.COLUMNS PK_COL ON PK_COL.COLUMN_ID = FK_COLS.REFERENCED_COLUMN_ID AND PK_COL.OBJECT_ID = PK_TAB.OBJECT_ID
JOIN $Args[0].INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK2 ON  FK2.CONSTRAINT_SCHEMA = schema_name(FK1.schema_id) AND FK2.CONSTRAINT_NAME = FK1.NAME
JOIN $Args[0].INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.CONSTRAINT_SCHEMA = FK2.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = FK2.CONSTRAINT_NAME
JOIN $Args[0].INFORMATION_SCHEMA.KEY_COLUMN_USAGE UQC ON UQC.CONSTRAINT_SCHEMA = FK2.UNIQUE_CONSTRAINT_SCHEMA AND UQC.CONSTRAINT_NAME = FK2.UNIQUE_CONSTRAINT_NAME AND UQC.TABLE_NAME = PK_TAB.NAME AND UQC.COLUMN_NAME = PK_COL.NAME
UNION
SELECT /*PK AND UNIQUE*/
TC.CONSTRAINT_NAME,  
TC.CONSTRAINT_TYPE,
TC.CONSTRAINT_CATALOG,
TC.CONSTRAINT_SCHEMA,
TC.TABLE_NAME,
IC.INDEX_COLUMN_ID ORDINAL_POSITION,
C.NAME COLUMN_NAME,
'' REFERENCED_TABLE_NAME,
'' REFERENCED_COLUMN_NAME,
'' REFERENCED_TABLE_SCHEMA,
CASE WHEN  PK.IS_UNIQUE_CONSTRAINT = 1 THEN IC.INDEX_COLUMN_ID ELSE 0 END POSITION_IN_UNIQUE_CONSTRAINT,
'' ON_UPDATE,
'' ON_DELETE,
'' ON_MATCH
FROM $Args[0].SYS.TABLES T1
JOIN $Args[0].INFORMATION_SCHEMA.TABLES T2 ON SCHEMA_NAME(T1.SCHEMA_ID) = T2.TABLE_SCHEMA AND T1.NAME = T2.TABLE_NAME
JOIN $Args[0].INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.TABLE_CATALOG = T2.TABLE_CATALOG AND TC.TABLE_SCHEMA = T2.TABLE_SCHEMA AND TC.TABLE_NAME = T2.TABLE_NAME AND (TC.CONSTRAINT_TYPE = 'PRIMARY KEY' OR TC.CONSTRAINT_TYPE = 'UNIQUE')
JOIN $Args[0].SYS.INDEXES PK ON T1.OBJECT_ID = PK.OBJECT_ID AND PK.NAME = TC.CONSTRAINT_NAME AND (PK.IS_UNIQUE_CONSTRAINT = 1 OR PK.IS_PRIMARY_KEY = 1)
JOIN $Args[0].SYS.INDEX_COLUMNS IC ON IC.OBJECT_ID = PK.OBJECT_ID AND IC.INDEX_ID = PK.INDEX_ID
JOIN $Args[0].SYS.COLUMNS C ON PK.OBJECT_ID = C.OBJECT_ID AND C.COLUMN_ID = IC.COLUMN_ID
) T`,
			sqlServer,
			info.NewCriterion(info.Catalog, "CONSTRAINT_CATALOG"),
			info.NewCriterion(info.Schema, "CONSTRAINT_SCHEMA"),
			info.NewCriterion(info.Table, "TABLE_NAME"),
		),

		// TIP: Use STUFF function with inner query instead of string_agg for SQL Server version < Server 2017 (14.x)
		info.NewQuery(info.KindIndexes, `SELECT 
T2.TABLE_CATALOG,
T2.TABLE_NAME,
I.TYPE INDEX_TYPE,
T2.TABLE_SCHEMA,
T2.TABLE_SCHEMA INDEX_SCHEMA,
I.NAME INDEX_NAME,
I.IS_UNIQUE INDEX_UNIQUE,
0 INDEX_POSITION,
'' INDEX_ORIGIN,
'' INDEX_PARTIAL,
STRING_AGG (CONVERT(NVARCHAR(MAX),C.NAME), ',') INDEX_COLUMNS
FROM $Args[0].SYS.TABLES T1
JOIN $Args[0].INFORMATION_SCHEMA.TABLES T2 ON T2.TABLE_SCHEMA = SCHEMA_NAME(T1.SCHEMA_ID) AND T2.TABLE_NAME = T1.NAME
JOIN $Args[0].SYS.INDEXES I ON T1.OBJECT_ID = I.OBJECT_ID 
JOIN $Args[0].SYS.INDEX_COLUMNS IC ON IC.OBJECT_ID = I.OBJECT_ID AND IC.INDEX_ID = I.INDEX_ID
JOIN $Args[0].SYS.COLUMNS C ON I.OBJECT_ID = C.OBJECT_ID AND C.COLUMN_ID = IC.COLUMN_ID
$WHERE
GROUP BY T2.TABLE_CATALOG, T2.TABLE_NAME, I.TYPE, T2.TABLE_SCHEMA, T1.SCHEMA_ID, I.NAME, I.IS_UNIQUE
`,
			sqlServer,
			info.NewCriterion(info.Catalog, "T2.TABLE_CATALOG"),
			info.NewCriterion(info.Schema, "T2.TABLE_SCHEMA"),
			info.NewCriterion(info.Table, "T2.TABLE_NAME"),
		),

		info.NewQuery(info.KindIndex, `SELECT 
T2.TABLE_CATALOG,
T2.TABLE_NAME,
I.TYPE INDEX_TYPE,
T2.TABLE_SCHEMA,
T2.TABLE_SCHEMA INDEX_SCHEMA,
I.NAME INDEX_NAME,
I.IS_UNIQUE INDEX_UNIQUE,
0 INDEX_POSITION,
'' INDEX_ORIGIN,
'' INDEX_PARTIAL,
STRING_AGG (CONVERT(NVARCHAR(MAX),C.NAME), ',') INDEX_COLUMNS
FROM $Args[0].SYS.TABLES T1
JOIN $Args[0].INFORMATION_SCHEMA.TABLES T2 ON T2.TABLE_SCHEMA = SCHEMA_NAME(T1.SCHEMA_ID) AND T2.TABLE_NAME = T1.NAME
JOIN $Args[0].SYS.INDEXES I ON T1.OBJECT_ID = I.OBJECT_ID 
JOIN $Args[0].SYS.INDEX_COLUMNS IC ON IC.OBJECT_ID = I.OBJECT_ID AND IC.INDEX_ID = I.INDEX_ID
JOIN $Args[0].SYS.COLUMNS C ON I.OBJECT_ID = C.OBJECT_ID AND C.COLUMN_ID = IC.COLUMN_ID
$WHERE
GROUP BY T2.TABLE_CATALOG, T2.TABLE_NAME, I.TYPE, T2.TABLE_SCHEMA, T1.SCHEMA_ID, I.NAME, I.IS_UNIQUE
`, sqlServer,
			info.NewCriterion(info.Catalog, "T2.TABLE_CATALOG"),
			info.NewCriterion(info.Schema, "T2.TABLE_SCHEMA"),
			info.NewCriterion(info.Table, "T2.TABLE_NAME"),
			info.NewCriterion(info.Index, "I.NAME"),
		),

		info.NewQuery(info.KindSequences, `SELECT
T.SEQUENCE_CATALOG,
T.SEQUENCE_SCHEMA,
T.SEQUENCE_NAME,
S.CURRENT_VALUE SEQUENCE_VALUE,
T.DATA_TYPE,
T.START_VALUE,
T.MAXIMUM_VALUE MAX_VALUE
FROM $Args[0].INFORMATION_SCHEMA.SEQUENCES T 
JOIN $Args[0].SYS.SEQUENCES S ON SCHEMA_NAME(S.SCHEMA_ID) = T.SEQUENCE_SCHEMA AND S.NAME = T.SEQUENCE_NAME`,
			sqlServer,
			info.NewCriterion(info.Catalog, "T.SEQUENCE_CATALOG"),
			info.NewCriterion(info.Schema, "T.SEQUENCE_SCHEMA"),
			info.NewCriterion(info.Sequence, "T.SEQUENCE_NAME"),
		),

		info.NewQuery(info.KindFunctions, `SELECT
ROUTINE_CATALOG,
ROUTINE_SCHEMA,
ROUTINE_NAME,
ROUTINE_BODY,
DATA_TYPE,
ROUTINE_TYPE,
CHARACTER_SET_NAME,
IS_DETERMINISTIC
FROM (
SELECT COALESCE(CASE WHEN '$Args[0]' = '' THEN NULL ELSE '$Args[0]' END, DB_NAME())  ROUTINE_CATALOG,
SCHEMA_NAME(OBJ.SCHEMA_ID) AS ROUTINE_SCHEMA,
OBJ.NAME AS ROUTINE_NAME,
MOD.DEFINITION ROUTINE_BODY,
COALESCE(TYPE_NAME(RET.USER_TYPE_ID),'') AS DATA_TYPE,
OBJ.TYPE_DESC ROUTINE_TYPE,
'' CHARACTER_SET_NAME,
'' IS_DETERMINISTIC
FROM $Args[0].SYS.OBJECTS OBJ
JOIN $Args[0].SYS.SQL_MODULES MOD ON MOD.OBJECT_ID = OBJ.OBJECT_ID AND OBJ.TYPE IN ('FN', 'TF', 'IF', 'P', 'X')
LEFT JOIN $Args[0].SYS.PARAMETERS RET ON OBJ.OBJECT_ID = RET.OBJECT_ID AND RET.PARAMETER_ID = 0
) T`,
			sqlServer,
			info.NewCriterion(info.Catalog, "ROUTINE_CATALOG"),
			info.NewCriterion(info.Schema, "ROUTINE_SCHEMA"),
			info.NewCriterion(info.Function, "ROUTINE_NAME"),
		),

		info.NewQuery(info.KindSession, `DECLARE @T TABLE (SPID INT
,STATUS VARCHAR(255)
,LOGIN  VARCHAR(255)
,HOSTNAME VARCHAR(255)
,BLKBY  VARCHAR(255)
,DBNAME  VARCHAR(255)
,COMMAND VARCHAR(255)
,CPUTIME INT
,DISKIO INT
,LASTBATCH VARCHAR(255)
,PROGRAMNAME VARCHAR(255)
,SPID2 INT
,REQUESTID INT)

INSERT INTO @T EXEC SP_WHO2

SELECT SPID PID,
LOGIN USER_NAME,
'' REGION,
COALESCE(DBNAME,'') CATALOG_NAME,
'' SCHEMA_NAME,
TRIM(COALESCE(PROGRAMNAME,'')) APP_NAME
FROM @T
`, sqlServer),

		info.NewQuery(info.KindForeignKeysCheckOff, `EXEC SP_MSFOREACHTABLE "ALTER TABLE ? NOCHECK CONSTRAINT ALL"`,
			sqlServer,
			info.NewCriterion(info.Catalog, ""),
			info.NewCriterion(info.Schema, ""),
			info.NewCriterion(info.Table, ""),
		),

		info.NewQuery(info.KindForeignKeysCheckOn, `EXEC SP_MSFOREACHTABLE "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"`,
			sqlServer,
			info.NewCriterion(info.Catalog, ""),
			info.NewCriterion(info.Schema, ""),
			info.NewCriterion(info.Table, ""),
		),
	)
	if err != nil {
		log.Printf("failed to register queries: %v", err)
	}
	registry.RegisterDialect(&info.Dialect{
		Product:                 sqlServer,
		Placeholder:             "@p",
		Transactional:           true,
		Insert:                  dialect.InsertWithMultiValues,
		Upsert:                  dialect.UpsertTypeMergeInto,
		Load:                    dialect.LoadTypeLocalData,
		QuoteCharacter:          '\'',
		CanAutoincrement:        true,
		CanLastInsertID:         false, //TODO ???
		AutoincrementFunc:       "",
		PlaceholderResolver:     new(PlaceHolderGenerator),
		DefaultPresetIDStrategy: dialect.PresetIDStrategyUndefined,
	})
}

var schemasDynamicSQLQuery = `DECLARE @QUERY NVARCHAR(MAX);
SET @QUERY =
(SELECT 'SELECT ''' + NAME + ''' AS CATALOG_NAME,
                NAME COLLATE DATABASE_DEFAULT AS SCHEMA_NAME, ' +
                ''''' SQL_PATH, ' +
	            '''' + CONVERT(VARCHAR(1000), COLLATIONPROPERTY( CONVERT(VARCHAR(1000),SERVERPROPERTY('COLLATION')) , 'CODEPAGE' )) + ''' DEFAULT_CHARACTER_SET_NAME, ' +
	            '''' + CONVERT(VARCHAR(1000), SERVERPROPERTY('COLLATION')) + ''' DEFAULT_COLLATION_NAME, ' +
	            ''''' REGION ' +
	    '  FROM ['+ NAME + '].SYS.SCHEMAS UNION ALL '
   FROM SYS.DATABASES
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)');

SET @QUERY = LEFT(@QUERY,LEN(@QUERY)-10) + ' ORDER BY CATALOG_NAME, SCHEMA_NAME';

DECLARE @T TABLE (CATALOG_NAME VARCHAR(1000), SCHEMA_NAME VARCHAR(1000), SQL_PATH VARCHAR(1000), DEFAULT_CHARACTER_SET_NAME VARCHAR(1000), DEFAULT_COLLATION_NAME VARCHAR(1000), REGION VARCHAR(1000))
INSERT @T EXECUTE (@QUERY);
SELECT *
FROM @T
`
